package com.pur.zsms.financing.dao.impl;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.check.vo.CheckVo;
import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.cost.dao.ICostDao;
import com.pur.zsms.financing.dao.IFinancingDao;
import com.pur.zsms.financing.vo.FinancingDetailVo;
import com.pur.zsms.financing.vo.FinancingManagerVo;
import com.pur.zsms.financing.vo.FinancingVo;
import com.pur.zsms.foodmanage.food.po.FoodPo;
import com.pur.zsms.matermanage.mater.po.MaterLevelPo;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.systemsetting.datadictionary.dao.IDataDictionaryDao;
import com.pur.zsms.systemsetting.datadictionary.po.Code;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;
import com.pur.zsms.systemsetting.datadictionary.vo.CodeVo;
import com.pur.zsms.timecontrol.dao.ITimeControlDao;
import com.pur.zsms.timecontrol.po.TimeControlPo;
import com.pur.zsms.turnover.dao.ITurnoverDao;
import com.pur.zsms.turnover.po.TurnoverPo;
import com.pur.zsms.turnover.po.TurnoverSubPo;
import com.pur.zsms.turnover.vo.TurnoverVo;

@Repository("financingDao")
public class FinancingDaoImpl extends GenericDao implements IFinancingDao {

	public PaginationSupport listTurnoverList(PageController pageController, TurnoverVo turnoverVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT a.`turnover_id`, a.cust_id, b.shop_name, CONCAT('￥',FORMAT(a.turnover_value_total, 2)), GROUP_CONCAT( c.code_name, ':￥', FORMAT(a2.turnover_value, 2), '<br/>' SEPARATOR '' ),a.`turnover_state`, a.turnover_date FROM y_turnover a, y_turnover_sub a2, t_shop b, t_code c WHERE b.shop_id = a.cust_id AND a.turnover_id=a2.turnover_id AND c.code_type_id = 'turnoverType' AND c.code_id = a2.turnover_type  ");
		if (turnoverVo.getCustId() != 0) {
			ql.append(" and a.cust_id = '" + turnoverVo.getCustId() + "'");
		}
		if (turnoverVo.getTurnoverDate() != null && !turnoverVo.getTurnoverDate().equals("")) {
			ql.append(" and a.turnover_date = '" + turnoverVo.getTurnoverDate() + "'");
		}
		ql.append(" GROUP BY a2.turnover_id ORDER BY a.turnover_id DESC  ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public List<TurnoverPo> getTurnoverPo(TurnoverVo turnoverVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from TurnoverPo o where 1=1 ");
		if (turnoverVo.getCustId() != 0) {
			ql.append(" and o.custId ='" + turnoverVo.getCustId() + "'");
		}
		if (turnoverVo.getTurnoverDate() != null && !"".equals(turnoverVo.getTurnoverDate())) {
			ql.append(" and o.turnoverDate ='" + turnoverVo.getTurnoverDate() + "'");
		}
		if (turnoverVo.getTurnoverType() != 0) {
			ql.append(" and o.turnoverType ='" + turnoverVo.getTurnoverType() + "'");
		}
		if (turnoverVo.getTurnoverState() != 0) {
			ql.append(" and o.turnoverState ='" + turnoverVo.getTurnoverState() + "'");
		}
		if (turnoverVo.getTurnoverMonth() != null && !"".equals(turnoverVo.getTurnoverMonth())) {
			ql.append(" and o.turnoverDate like '" + turnoverVo.getTurnoverMonth() + "%'");
		}

		List<TurnoverPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object> getSelectMaterConcentration(FinancingManagerVo tFinancingManagerVo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(s1.ord_value_real) FROM t_order_supplier s1 WHERE s1.ord_cust_id='" + tFinancingManagerVo.getCustId()
				+ "' AND s1.ord_date>'" + tFinancingManagerVo.getDateStart() + "' AND s1.ord_date<'" + tFinancingManagerVo.getDateEnd()
				+ "' AND ord_sup_id!=0");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object> getSelectMaterSelf(FinancingManagerVo tFinancingManagerVo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(s1.ord_value_real) FROM t_order_supplier s1 WHERE s1.ord_cust_id='" + tFinancingManagerVo.getCustId()
				+ "' AND s1.ord_date>'" + tFinancingManagerVo.getDateStart() + "' AND s1.ord_date<'" + tFinancingManagerVo.getDateEnd()
				+ "' AND ord_sup_id =0");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object> getCostAllValue(FinancingManagerVo tFinancingManagerVo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(s1.cost_value_total) FROM c_cost s1 WHERE s1.cust_id='" + tFinancingManagerVo.getCustId() + "' AND s1.cost_date>='"
				+ tFinancingManagerVo.getDateStart() + "' AND s1.cost_date<='" + tFinancingManagerVo.getDateEnd() + "' ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object> getCostAllValueGroupByCostType(FinancingManagerVo tFinancingManagerVo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(s2.cost_value) FROM c_cost_sub s2 , c_cost s1 WHERE s2.cost_id=s1.cost_id AND s1.cost_date >= '"
				+ tFinancingManagerVo.getDateStart() + "' AND s1.cost_date <= '" + tFinancingManagerVo.getDateEnd() + "' AND s1.cust_id = '"
				+ tFinancingManagerVo.getCustId() + "' AND s2.cost_type='" + tFinancingManagerVo.getCostType() + "' ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object> getTurnoverAllValueGroupByTurnoverType(FinancingManagerVo tFinancingManagerVo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(s2.turnover_value) FROM y_turnover_sub s2 , y_turnover s1 WHERE s2.turnover_id=s1.turnover_id AND s1.turnover_date >= '"
				+ tFinancingManagerVo.getDateStart()
				+ "' AND s1.turnover_date <= '"
				+ tFinancingManagerVo.getDateEnd()
				+ "' AND s1.cust_id = '"
				+ tFinancingManagerVo.getCustId() + "' AND s2.turnover_type='" + tFinancingManagerVo.getTurnoverType() + "' ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<Object> getTurnoverAllValue(FinancingManagerVo tFinancingManagerVo) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT SUM(s1.turnover_value_total) FROM y_turnover s1 WHERE s1.cust_id='" + tFinancingManagerVo.getCustId()
				+ "' AND s1.turnover_date>='" + tFinancingManagerVo.getDateStart() + "' AND s1.turnover_date<='" + tFinancingManagerVo.getDateEnd()
				+ "' ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	@Override
	public List<Object[]> getUserIdAndUserName(String custId) {

		List list = this.entityManager.createNativeQuery(
				" SELECT s.`shop_id`,s.`shop_name` FROM t_shop s WHERE shop_id IN (" + custId + ") ORDER BY shop_id").getResultList();

		return list;
	}

	public PaginationSupport findSelectMaterConcentrationDetail(PageController pageController, FinancingDetailVo tFinancingDetailVo)
			throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("   SELECT a. ord_id , b. ord_cust_id , u.shop_name , b.ord_date , a.ord_mater_id , m.mater_name , m. specifications , m. brands , a. ord_mater_weight , m. unit , a. ord_mater_value , a. ord_sup_id , s. sup_companyname FROM t_order_supplier_mater_reality a, t_order_custom b, t_shop u, t_material m, t_supplier s WHERE a. return_goods != - 1 AND a. ord_id = b. ord_id AND b. ord_cust_id = u.shop_id AND m. mater_id = a. ord_mater_id AND s. sup_id = a. ord_sup_id");
		ql.append(" AND b. ord_cust_id IN (" + tFinancingDetailVo.getCustId() + ")");

		if (tFinancingDetailVo.getSupwayFlag() == 1) {
			ql.append(" AND a.ord_sup_id != 0");
		} else if (tFinancingDetailVo.getSupwayFlag() == 2) {
			ql.append(" AND a.ord_sup_id = 0");
		}

		ql.append(" AND ord_date >= '" + tFinancingDetailVo.getOrdDateStart() + "' AND ord_date <= '" + tFinancingDetailVo.getOrdDateEnd()
				+ "' ORDER BY b.ord_cust_id,b. ord_date ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

}
